﻿using AntUnion.FrameWork.Model.Models;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using System.Linq;
using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using TencentCloud.Tci.V20190318.Models;
using Microsoft.AspNetCore.Http;
using AntUnion.FrameWork.Util;
using AntUnion.FrameWork.Web.Common;
using AntUnion.FrameWork.Web.Extend;
using System.Collections.Generic;
using System.IO;
using TencentCloud.Common.Profile;
using System.Net.Http.Headers;
using System.Data;
using System.Security.Policy;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using static System.Net.WebRequestMethods;
using NPOI.SS.Formula.Functions;
using System.Diagnostics.Contracts;
using Util.Extension;
using System.Dynamic;
using static AntUnion.FrameWork.Web.Areas.Console.Controllers.InformationController;
using TencentCloud.Ame.V20190916.Models;
using TencentCloud.Cpdp.V20190820.Models;
using Newtonsoft.Json;
using System.Text.RegularExpressions;
using NPOI.HPSF;
using NPOI.XSSF.UserModel;

namespace AntUnion.FrameWork.Web.Areas.Console.Controllers
{
    [Area("Console")]
    public class InformationController : BaseController
    {
        private readonly _antunionContext _context;
        private IWebHostEnvironment _env;
        public InformationController(_antunionContext context, IWebHostEnvironment env) : base(context, env)
        {
            _context = context;
            _env = env;
        }

        #region 线索指挥仓

        public IActionResult Chart()
        {
            return View();
        }

        #endregion

        #region 模型数据分析

        /// <summary>
        /// 模型数据分析
        /// </summary>
        /// <returns></returns>
        public IActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 审查数据分析
        /// </summary>
        /// <returns></returns>
        public IActionResult Arrest()
        {
            return View();
        }

        /// <summary>
        /// 一审公诉数据分析
        /// </summary>
        /// <returns></returns>
        public IActionResult Prosecution()
        {
            return View();
        }

        /// <summary>
        /// 获取身份证信息列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="limit"></param>
        /// <param name="name"></param>
        /// <param name="personname"></param>
        /// <param name="idcard"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<JsonResult> GetInfomation(int page, int limit, string number, string name, string personname, string idcard, string cause, string nature, string suspectedcause, string results, string type)
        {
            ListResult res = new ListResult();
            try
            {

                var data = from infor in _context.Information
                           join person in _context.Personinfos
                           on new { A = infor.Number, B = infor.Personname } equals new { A = person.Number, B = person.Personname }
                           into dc
                           from dci in dc.DefaultIfEmpty()
                           select new
                           {
                               id = infor.Id,
                               number = infor.Number,
                               name = infor.Name,
                               personname = infor.Personname,
                               idcard = dci.Idcard,
                               cause = infor.Cause,
                               nature = infor.Nature,
                               joinpersonnumber = infor.Joinpersonnumber,
                               suspectedcause = infor.Suspectedcause,
                               date = infor.Date,
                               results = infor.Results,
                               sentencetime = infor.Sentencetime,
                               type = infor.Type,
                               addtime = infor.Addtime,
                               updatetime = infor.Updatetime
                           };
                if (!string.IsNullOrEmpty(number))
                {
                    data = data.Where(k => k.number.Contains(number));
                }
                if (!string.IsNullOrEmpty(name))
                {
                    data = data.Where(k => k.name.Contains(name));
                }
                if (!string.IsNullOrEmpty(personname))
                {
                    data = data.Where(k => k.name.Contains(personname));
                }
                if (!string.IsNullOrEmpty(idcard))
                {
                    data = data.Where(k => k.idcard.Contains(idcard));
                }
                if (!string.IsNullOrEmpty(cause))
                {
                    data = data.Where(k => k.cause.Contains(cause));
                }
                if (!string.IsNullOrEmpty(nature))
                {
                    data = data.Where(k => k.nature.Contains(nature));
                }
                if (!string.IsNullOrEmpty(suspectedcause))
                {
                    data = data.Where(k => k.suspectedcause.Contains(suspectedcause));
                }
                if (!string.IsNullOrEmpty(results))
                {
                    data = data.Where(k => k.results == results);
                }
                if (!string.IsNullOrEmpty(type))
                {
                    data = data.Where(k => k.type == type);
                }
                res.Data = await data.OrderBy(k => k.id).Skip((page - 1) * limit).Take(limit).ToListAsync();
                res.Code = 0;
                res.Count = data.Count();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        /// <summary>
        /// 删除案件信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<JsonResult> Delete(int id)
        {
            ListResult res = new ListResult();
            try
            {
                Information information = await _context.Information.FindAsync(id);
                _context.Information.Remove(information);
                res.Code = 0;
                res.Data = await _context.SaveChangesAsync();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        /// <summary>
        /// 删除所有案件信息
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<JsonResult> DeleteAll(string type)
        {
            ListResult res = new ListResult();
            try
            {
                _context.Database.ExecuteSqlRaw($"delete from information where type='{type}'");
                res.Code = 0;
                res.Data = await _context.SaveChangesAsync();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        #endregion

        #region 模型线索列表

        /// <summary>
        /// 审查数据分析
        /// </summary>
        /// <returns></returns>
        public IActionResult ArrestClue()
        {
            return View();
        }

        /// <summary>
        /// 一审公诉数据分析
        /// </summary>
        /// <returns></returns>
        public IActionResult ProsecutionClue()
        {
            return View();
        }

        /// <summary>
        /// 获取线索信息
        /// </summary>
        /// <param name="page"></param>
        /// <param name="limit"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<JsonResult> GetClue(int page, int limit, string type)
        {
            ListResult res = new ListResult();
            try
            {
                //获取设置
                List<TbSystemConfig> tbSystemConfigs = _context.TbSystemConfigs.ToList();
                TbSystemConfig _charges = tbSystemConfigs.Where(k => k.MenuName == "charges").FirstOrDefault();
                List<int> chargesList = JsonConvert.DeserializeObject<List<int>>(_charges.Value);
                TbSystemConfig _nature = tbSystemConfigs.Where(k => k.MenuName == "nature").FirstOrDefault();
                List<int> _natureList = JsonConvert.DeserializeObject<List<int>>(_nature.Value);

                List<string> chargesNameList = _context.Charges.Where(k => chargesList.Contains(k.Id)).Select(k => k.Name).ToList();
                List<string> natureNameList = _context.Natures.Where(k => chargesList.Contains(k.Id)).Select(k => k.Name).ToList();
                var data = _context.Information.Where(k => chargesNameList.Contains(k.Cause) && natureNameList.Contains(k.Nature));
                res.Data = await data.OrderBy(k => k.Id).Skip((page - 1) * limit).Take(limit).ToListAsync();
                res.Code = 0;
                res.Count = data.Count();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        /// <summary>
        /// 删除统计
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public async Task<JsonResult> DeleteStatistics(string type)
        {
            ListResult res = new ListResult();
            try
            {
                _context.Database.ExecuteSqlRaw($"delete from personcount where type='{type}'");
                res.Code = 0;
                res.Data = await _context.SaveChangesAsync();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        /// <summary>
        /// 案件统计
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<JsonResult> GetPerson(string type)
        {
            ListResult res = new ListResult();
            try
            {
                string sql = "";
                //删除数据
                _context.Database.ExecuteSqlRaw($"delete from personcount where type='{type}'");


                //获取设置
                long jointime = 3;
                List<TbSystemConfig> systemConfigs = _context.TbSystemConfigs.ToList();
                string str_jointime = systemConfigs.Where(k => k.MenuName == "jointime").Select(k => k.Value).FirstOrDefault();
                if (!string.IsNullOrEmpty(str_jointime))
                {
                    jointime = int.Parse(str_jointime);
                }
                List<TbSystemConfig> tbSystemConfigs = _context.TbSystemConfigs.ToList();
                TbSystemConfig _charges = tbSystemConfigs.Where(k => k.MenuName == "charges").FirstOrDefault();
                List<int> chargesList = JsonConvert.DeserializeObject<List<int>>(_charges.Value);
                TbSystemConfig _nature = tbSystemConfigs.Where(k => k.MenuName == "nature").FirstOrDefault();
                List<int> natureList = JsonConvert.DeserializeObject<List<int>>(_nature.Value);
                List<string> chargesNameList = _context.Charges.Where(k => chargesList.Contains(k.Id)).Select(k => k.Name).ToList();
                List<string> natureNameList = _context.Natures.Where(k => natureList.Contains(k.Id)).Select(k => k.Name).ToList();
                string chargesWhere = "";
                foreach (string item in chargesNameList)
                {
                    chargesWhere += $"'{item}',";
                }
                if (chargesWhere.Length > 0)
                {
                    chargesWhere = chargesWhere.Substring(0, chargesWhere.Length - 1);
                }
                else
                {
                    chargesWhere = "";
                }
                string natureWhere = "";
                foreach (string item in natureNameList)
                {
                    natureWhere += $"'{item}',";
                }
                if (natureWhere.Length > 0)
                {
                    natureWhere = natureWhere.Substring(0, natureWhere.Length - 1);
                }
                else
                {
                    natureWhere = "";
                }
                //查询符合条件的，并且涉案次数查过jointime次的相关案件信息
                sql = $"insert into personcount(personname,idcard,count,joinpersonnumber,type,pid,sort) " +
                    $"select p.personname,p.idcard,count(*) as count,0,'{type}',0,0 from information i join personinfo p " +
                    $"on i.number = p.number and i.personname = p.personname " +
                    $"where i.suspectedcause in({chargesWhere}) and i.nature in({natureWhere}) and i.type='{type}'" +
                    $"group by p.idcard having count >= {jointime} order by count desc";
                int personcount = await _context.Database.ExecuteSqlRawAsync(sql);
                List<int> ids = _context.Personcounts.Where(k => k.Type == type && k.Pid == 0).Select(k => k.Id).ToList();
                res.Code = 0;
                res.Data = ids;
                res.Msg = $"计算出出现次数超过{ids.Count}的人员数据";
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        [HttpPost]
        public async Task<JsonResult> CaseStatistics(int id, string type)
        {
            ListResult res = new ListResult();
            try
            {
                //获取团伙成员
                string sql = $"select DISTINCT personname from information where number in(select number from personinfo where idcard=(select idcard from personcount where id={id}))";
                List<dynamic> personName = cmdSql(sql);
                string joinperson = "";
                foreach (var item in personName)
                {
                    joinperson += item.personname + "、";
                }
                if (joinperson.Length > 0)
                {
                    joinperson = joinperson.Substring(0, joinperson.Length - 1);
                }
                Personcount personcounts = _context.Personcounts.Where(k => k.Id == id).FirstOrDefault();
                personcounts.Joinperson = joinperson;
                personcounts.Joinpersonnumber = personName.Count;
                personcounts.Counttype = "统计数据";
                personcounts.Cause = "展开";
                personcounts.Pid = 0;
                personcounts.Sort = 0;
                personcounts.Type = type.ToString();
                //添加标题
                Personcount title = new Personcount();
                title.Personname = "案件号";
                title.Idcard = "案件名称";
                title.Joinperson = "罪名";
                title.Cause = "案由";
                title.Counttype = "标题";
                title.Pid = id;
                title.Sort = 0;
                title.Type = type.ToString();
                _context.Personcounts.Add(title);
                List<Information> informationList = _context.Information.Where(k => _context.Personinfos.Where(k => k.Idcard == _context.Personcounts.First(k => k.Id == id).Idcard).Select(k => k.Number).Contains(k.Number)).Distinct().OrderBy(k => k.Addtime).ToList();
                if (informationList.Count > 0)
                {
                    int sort = 1;
                    informationList.ForEach(item =>
                    {
                        Personcount info = new Personcount();
                        info.Personname = item.Number;
                        info.Idcard = item.Name;
                        info.Joinperson = item.Suspectedcause;
                        info.Counttype = "案件内容";
                        info.Cause = item.Cause;
                        info.Pid = id;
                        info.Sort = sort++;
                        info.Type = type.ToString();
                        _context.Personcounts.Add(info);
                    });
                }
                res.Code = 0;
                res.Data = await _context.SaveChangesAsync();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        /// <summary>
        /// 获取统计数据
        /// </summary>
        /// <param name="page"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<JsonResult> GetStatistics(string type)
        {
            ListResult res = new ListResult();
            try
            {
                //涉案次数
                int joinpersonranking = 3;
                List<TbSystemConfig> systemConfigs = _context.TbSystemConfigs.ToList();
                string str_joinpersonranking = systemConfigs.Where(k => k.MenuName == "joinpersonranking").Select(k => k.Value).FirstOrDefault();
                if (!string.IsNullOrEmpty(str_joinpersonranking))
                {
                    joinpersonranking = int.Parse(str_joinpersonranking);
                }
                //string sql = $"select joinpersonnumber,count(*) as count from personcount where type='{type}' group by joinpersonnumber order by joinpersonnumber desc LIMIT {joinpersonranking}";
                //List<dynamic> jointimeList = cmdSql(sql);
                ////如果有第三名，确认第三名的出现次数，出现次数大于1，取前二，并且出现次数≥3
               
                //if (jointimeList.Count >= joinpersonranking)
                //{
                //    //确认最后一名的涉案次数
                //    long count = jointimeList.LastOrDefault().count;
                //    //如果第三名有多条
                //    if (count > 1)
                //    {
                //        //取倒二的涉案次数
                //        joinpersonnumber = jointimeList[jointimeList.Count - 2].joinpersonnumber;
                //    }
                //    else
                //    {
                //        joinpersonnumber = jointimeList.LastOrDefault().joinpersonnumber;
                //    }
                //}
                //else
                //{
                //    if (jointimeList.Count == 0)
                //    {
                //        joinpersonnumber = 3;
                //    }
                //    else
                //    {
                //        joinpersonnumber = jointimeList.LastOrDefault().joinpersonnumber;
                //    }
                //}
                var data = from personcount in _context.Personcounts.Where(k => k.Type == type)
                           select new
                           {
                               id = personcount.Id,
                               personname = personcount.Personname,
                               idCard = personcount.Idcard,
                               joinperson = personcount.Joinperson,
                               joinpersonnumber = personcount.Joinpersonnumber,
                               cause = personcount.Cause,
                               pid = personcount.Pid,
                               sort = personcount.Sort,
                           };
                data=data.Where(k => k.joinpersonnumber >= joinpersonranking || k.joinpersonnumber==0);
                res.Data = await data.OrderByDescending(k => k.joinpersonnumber).ThenBy(k => k.sort).ToListAsync();
                res.Code = 0;
                res.Count = data.Count();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        #endregion

        #region 导出

        [HttpGet]
        public JsonResult Export(string type)
        {
            Result re = new Result();
            try
            {
                //涉案次数
                int joinpersonranking = 3;
                List<TbSystemConfig> systemConfigs = _context.TbSystemConfigs.ToList();
                string str_joinpersonranking = systemConfigs.Where(k => k.MenuName == "joinpersonranking").Select(k => k.Value).FirstOrDefault();
                if (!string.IsNullOrEmpty(str_joinpersonranking))
                {
                    joinpersonranking = int.Parse(str_joinpersonranking);
                }
                var data = from personcount in _context.Personcounts.Where(k => k.Type == type)
                           select new ExportModel
                           {
                               id = personcount.Id,
                               personname = personcount.Personname,
                               idCard = personcount.Idcard,
                               joinperson = personcount.Joinperson,
                               joinpersonnumber = personcount.Joinpersonnumber,
                               cause = personcount.Cause,
                               pid = personcount.Pid,
                               sort = personcount.Sort,
                           };
                data = data.Where(k => k.joinpersonnumber >= joinpersonranking || k.joinpersonnumber == 0);
                List<ExportModel> exportModelList = data.OrderByDescending(k => k.joinpersonnumber).ThenBy(k => k.sort).ToList();
                string[] sColumnName = new string[] { "序号", "涉案人员", "身份证号", "可能涉及团伙的成员", "相关案件号查询" };
                string msg = "";
                string sFileName = "线索列表" + DateTime.Now.ToString("yyyyMMddHHmmss");
                bool res =OfficeHelper.ModelExportEPPlusExcel<ExportModel>(exportModelList, null, "\\Excel", "线索列表", sFileName, sColumnName, ref msg);
                //res.Data = await data.OrderByDescending(k => k.joinpersonnumber).ThenBy(k => k.sort).ToListAsync();
                //res.Code = 0;
                //res.Count = data.Count();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                re.Code = -1;
                //re.Msg = ex.Message;
                re.Msg = "导出失败，异常内容：" + ex.Message;
            }
            return new JsonResult(re);
        }

        public class ExportModel
        {
            public int id { get; set; }
            public string personname { get; set; }
            public string idCard { get; set; }
            public string joinperson { get; set; }
            public int joinpersonnumber { get; set; }
            public string cause { get; set; }
            public int pid { get; set; }
            public int sort { get; set; }
        }
        #endregion

        #region 导入文档

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <returns></returns>
        public JsonResult Import()
        {
            Result re = new Result();
            try
            {
                IFormFile file = Request.Form.Files[0];
                long size = file.Length;
                if (size > 10485760 )
                {
                    re.Code = 1;
                    re.Msg = "上传的文件不能大于10M！";
                }
                else
                {
                    MemoryStream ms = new MemoryStream();
                    file.CopyTo(ms);
                    ms.Seek(0, SeekOrigin.Begin);
                    IWorkbook workbook =null;
                   var contentType = Path.GetExtension(file.FileName);
                    if (contentType == ".xls")
                    {
                        workbook = new HSSFWorkbook(ms);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(ms);
                    }
                    ISheet sheet = workbook.GetSheetAt(0);
                    IRow dataRow0 = sheet.GetRow(0);
                    //读取协议内容
                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow dataRow = sheet.GetRow(i);
                        string type = dataRow.Cells.Count == 10 ? "1" : "0";
                        if (dataRow0.Cells.Count == 10)
                        {
                            string _Number = dataRow.GetCell(0) == null ? "" : dataRow.GetCell(0).ToString().Trim();
                            string _Name = dataRow.GetCell(1) == null ? "" : dataRow.GetCell(1).ToString().Trim();
                            string _Personname = dataRow.GetCell(2) == null ? "" : dataRow.GetCell(2).ToString().Trim();
                            string _Nature = dataRow.GetCell(3) == null ? "" : dataRow.GetCell(3).ToString().Trim();
                            string _Joinpersonnumber = dataRow.GetCell(4) == null ? "" : dataRow.GetCell(4).ToString().Trim();
                            string _Cause = dataRow.GetCell(5) == null ? "" : dataRow.GetCell(5).ToString().Trim();
                            string _Date = "";
                            try
                            {
                                if (dataRow.GetCell(6) == null)
                                {
                                    _Date = "";
                                }
                                else
                                {
                                    if (dataRow.GetCell(6).CellType.ToString() == "Blank")
                                    {
                                        _Date = "";
                                    }
                                    else
                                    {
                                        _Date = dataRow.GetCell(6).DateCellValue.ToString("yyyy-MM-dd").Trim();
                                    }
                                }
                            }
                            catch (Exception)
                            {
                                _Date = "";
                            }

                            string _Suspectedcause = dataRow.GetCell(7) == null ? "" : dataRow.GetCell(7).ToString().Trim();
                            string _Sentencetime = dataRow.GetCell(8) == null ? "" : dataRow.GetCell(8).ToString().Trim();
                            string _Results = dataRow.GetCell(9) == null ? "" : dataRow.GetCell(9).ToString().Trim();

                            Information _information = _context.Information.Where(k => k.Number == _Number && k.Personname == _Personname && k.Type == type).FirstOrDefault();
                            if (_information == null)
                            {
                                Information information = new Information();
                                information.Number = _Number;
                                information.Name = _Name;
                                information.Personname = _Personname;
                                information.Cause = _Cause;
                                information.Nature = _Nature;
                                information.Joinpersonnumber = _Joinpersonnumber;
                                information.Suspectedcause = _Suspectedcause;
                                information.Date = _Date;
                                information.Results = _Results;
                                information.Sentencetime = _Sentencetime;
                                information.Type = type;
                                information.Addtime = DateTime.Now;
                                information.Updatetime = DateTime.Now;
                                _context.Information.Add(information);
                            }
                            else
                            {
                                //如果重复提交，更新最后更新时间
                                _information.Updatetime = DateTime.Now;
                            }
                        }
                        else if (dataRow0.Cells.Count == 9)
                        {
                            string _Number = dataRow.GetCell(0) == null ? "" : dataRow.GetCell(0).ToString().Trim();
                            string _Name = dataRow.GetCell(1) == null ? "" : dataRow.GetCell(1).ToString().Trim();
                            string _Personname = dataRow.GetCell(2) == null ? "" : dataRow.GetCell(2).ToString().Trim();
                            string _Cause = dataRow.GetCell(3) == null ? "" : dataRow.GetCell(3).ToString().Trim();
                            string _Nature = dataRow.GetCell(4) == null ? "" : dataRow.GetCell(4).ToString().Trim();
                            string _Joinpersonnumber = dataRow.GetCell(5) == null ? "" : dataRow.GetCell(5).ToString().Trim();
                            string _Suspectedcause = dataRow.GetCell(6) == null ? "" : dataRow.GetCell(6).ToString().Trim();
                            string _Date = "";
                            try
                            {
                                if (dataRow.GetCell(7) == null)
                                {
                                    _Date = "";
                                }
                                else
                                {
                                    if (dataRow.GetCell(7).CellType.ToString() == "Blank")
                                    {
                                        _Date = "";
                                    }
                                    else
                                    {
                                        _Date = dataRow.GetCell(7).DateCellValue.ToString("yyyy-MM-dd").Trim();
                                    }
                                }
                            }
                            catch (Exception)
                            {
                                _Date = "";
                                ;
                            }
                            string _Results = dataRow.GetCell(8) == null ? "" : dataRow.GetCell(8).ToString().Trim();
                            Information _information = _context.Information.Where(k => k.Number == _Number && k.Personname == _Personname && k.Type == type).FirstOrDefault();
                            if (_information == null)
                            {
                                Information information = new Information();
                                information.Number = _Number;
                                information.Name = _Name;
                                information.Personname = _Personname;
                                information.Cause = _Cause;
                                information.Nature = _Nature;
                                information.Joinpersonnumber = _Joinpersonnumber;
                                information.Suspectedcause = _Suspectedcause;
                                information.Date = _Date;
                                information.Results = _Results;
                                information.Type = type;
                                information.Addtime = DateTime.Now;
                                information.Updatetime = DateTime.Now;
                                _context.Information.Add(information);
                            }
                            else
                            {
                                //如果重复提交，更新最后更新时间
                                _information.Updatetime = DateTime.Now;
                            }
                        }
                        else if (dataRow0.Cells.Count == 3)
                        {
                            string _Number = dataRow.GetCell(0).StringCellValue.Trim();
                            string _Personname = dataRow.GetCell(1).StringCellValue.Trim();
                            string _Idcard = dataRow.GetCell(2).StringCellValue.Trim();
                            Personinfo _personinfo = _context.Personinfos.Where(k => k.Number == _Number && k.Personname == _Personname && k.Idcard == _Idcard).FirstOrDefault();
                            if (_personinfo == null)
                            {
                                Personinfo person = new Personinfo();
                                person.Number = dataRow.GetCell(0).StringCellValue.Trim();
                                person.Personname = dataRow.GetCell(1).StringCellValue.Trim();
                                person.Idcard = dataRow.GetCell(2).StringCellValue.Trim();
                                person.Addtime = DateTime.Now;
                                person.Updatetime = DateTime.Now;
                                _context.Personinfos.Add(person);
                            }
                            else
                            {
                                //如果重复提交，更新最后更新时间
                                _personinfo.Updatetime = DateTime.Now;
                            }
                        }
                        else
                        {
                            re.Code = -1;
                            re.Msg = "请导入正确格式的文档";
                        }
                    }
                    int count = _context.SaveChanges();
                    ms.Close();
                    ms.Dispose();
                    re.Code = 0;
                    re.Msg = $"成功导入{count}条数据";
                }
            }
            catch (Exception ex)
            {
                re.Code = -1;
                //re.Msg = ex.Message;
                re.Msg = "导入失败，请导入正确格式的文档，异常内容：" + ex.Message;
            }
            return new JsonResult(re);
        }

        #endregion

        #region 模型算子设置

        public IActionResult Setting()
        {
            List<TbSystemConfig> data = _context.TbSystemConfigs.ToList();
            ViewBag.jointime = data.Where(k => k.MenuName == "jointime").FirstOrDefault().Value;
            ViewBag.joinpersonranking = data.Where(k => k.MenuName == "joinpersonranking").FirstOrDefault().Value;
            return View();
        }

        /// <summary>
        /// 获取身份证信息列表
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<JsonResult> GetCharge()
        {
            ListResult res = new ListResult();
            try
            {
                var data = from infor in _context.Charges
                           select new
                           {
                               value = infor.Id,
                               title = infor.Name,
                           };
                TbSystemConfig tbSystemConfig = _context.TbSystemConfigs.Where(k => k.MenuName == "charges").FirstOrDefault();
                if (tbSystemConfig != null)
                {
                    res.Value = JsonConvert.DeserializeObject<int[]>(tbSystemConfig.Value);
                }
                res.Data = await data.OrderBy(k => k.value).ToListAsync();
                res.Code = 0;
                res.Count = data.Count();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        /// <summary>
        /// 获取身份证信息列表
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<JsonResult> GetNature()
        {
            ListResult res = new ListResult();
            try
            {
                var data = from infor in _context.Natures
                           select new
                           {
                               value = infor.Id,
                               title = infor.Name,
                           };
                TbSystemConfig tbSystemConfig = _context.TbSystemConfigs.Where(k => k.MenuName == "nature").FirstOrDefault();
                if (tbSystemConfig != null)
                {
                    res.Value = JsonConvert.DeserializeObject<int[]>(tbSystemConfig.Value);
                }
                res.Data = await data.OrderBy(k => k.value).ToListAsync();
                res.Code = 0;
                res.Count = data.Count();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        /// <summary>
        /// 保存设置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<JsonResult> SaveSetting(int jointime, int joinpersonranking, List<TransferValue> charges, List<TransferValue> nature)
        {
            ListResult res = new ListResult();
            try
            {
                List<TbSystemConfig> tbSystemConfigs = _context.TbSystemConfigs.ToList();
                TbSystemConfig _jointime = tbSystemConfigs.Where(k => k.MenuName == "jointime").FirstOrDefault();
                _jointime.Value = jointime.ToString();
                TbSystemConfig _joinpersonranking = tbSystemConfigs.Where(k => k.MenuName == "joinpersonranking").FirstOrDefault();
                _joinpersonranking.Value = joinpersonranking.ToString();
                TbSystemConfig _charges = tbSystemConfigs.Where(k => k.MenuName == "charges").FirstOrDefault();
                List<int> chargesArr = new List<int>();
                foreach (TransferValue item in charges)
                {
                    chargesArr.Add(int.Parse(item.value));
                }
                chargesArr.Sort();
                _charges.Value = JsonConvert.SerializeObject(chargesArr);
                TbSystemConfig _nature = tbSystemConfigs.Where(k => k.MenuName == "nature").FirstOrDefault();
                List<int> natureArr = new List<int>();
                foreach (TransferValue item in nature)
                {
                    natureArr.Add(int.Parse(item.value));
                }
                natureArr.Sort();
                _nature.Value = JsonConvert.SerializeObject(natureArr);
                res.Code = 0;
                res.Data = await _context.SaveChangesAsync();
                return new JsonResult(res);
            }
            catch (Exception ex)
            {
                res.Code = 1;
                res.Msg = ex.Message;
                return new JsonResult(res);
            }
        }

        public class TransferValue
        {
            public string value { get; set; }
            public string title { get; set; }
        }

        #endregion

        #region 分析线索管理

        /// <summary>
        /// 审查数据分析
        /// </summary>
        /// <returns></returns>
        public IActionResult ArrestClueData()
        {
            return View();
        }

        /// <summary>
        /// 一审公诉数据分析
        /// </summary>
        /// <returns></returns>
        public IActionResult ProsecutionClueData()
        {
            return View();
        }

        #endregion

        public List<dynamic> cmdSql(string sql)
        {
            try
            {
                using (var cmd = _context.Database.GetDbConnection().CreateCommand())
                {
                    cmd.CommandText = sql;
                    if (cmd.Connection.State != ConnectionState.Open)
                        cmd.Connection.Open();

                    var retObject = new List<dynamic>();
                    using (var dataReader = cmd.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            var dataRow = new ExpandoObject() as IDictionary<string, object>;
                            for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++)
                            {
                                dataRow.Add(
                                    dataReader.GetName(iFiled),
                                    dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] // use null instead of {}
                                );
                            }
                            retObject.Add((ExpandoObject)dataRow);
                        }
                        return retObject;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
